﻿--部门客户拜访记录
create proc [dbo].[proc_CheckInOut_GetDepartmentList]
(
	@username nvarchar(50),
	@DepartmentId varchar(2000),
	@SearchTxt varchar(100)
)
as
--公司Id
declare @companyid varchar (4)
set @companyid = (select companyid from Employee where username=@username)
declare @sql nvarchar (2000)
set @sql =
	+'select' 
		+' Id,'
		+' CompanyId,'
		+' CustomerId,'
		+'(select CustomerName from Customer where CheckInOut.CustomerId = Customer.CustomerId) as Customer,'
		+' ContactPerson,'
		+' ContactTel,'
		+' Address,'
		+' ISNULL(MainProject,'''') AS MainProject,'
		+' ISNULL(Content,''无'')AS Content,'
		+' ISNULL(NextMeet,'''') AS NextMeet,'
		+' UserName,'
		+' ISNULL(CheckInTime,'''')AS CheckInTime,'
		+' CheckOutTime,'
		+' CheckInTime'
	+' from' 
		+' CheckInOut'
	+' where' 
		  +' CompanyId = '+@CompanyId
		  +' and (UserName in(select username from employee where departmentid in ('+@DepartmentId+')))'
		  +' and (select CustomerName from Customer where CheckInOut.CustomerId = Customer.CustomerId) like ''%'+@SearchTxt+'%'''


exec (@sql)
